package cn.audit.repository.operation;

import cn.audit.bean.pojo.Project1;
import cn.audit.bean.dto.UserCorrelation;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author fei人类
 * @date 2019/05/20
 */
public interface ProjectsItemRepository extends JpaRepository<Project1, Long>, JpaSpecificationExecutor<Project1> {

    /**
     * 关联查询项目基本信息
     */
    @Query("select new cn.audit.bean.dto.UserCorrelation(u,r,p) " +
            "from User u,AuditInstitution r,Project1 p " +
            "where p.lord =u.id and p.institutions = r.id ")
    List<UserCorrelation> findItemAll();

    /**
     * 关联根据Id查询项目基本信息
     */
    @Query("select new cn.audit.bean.dto.UserCorrelation(p,i) " +
            "from Project1 p,AuditInstitution i " +
            "where p.institutions = i.id and p.id= ?1 ")
    UserCorrelation findItemById(Long id);

    /**
     * 关联根据Id查询项目审计方案
     */
    @Query("select new cn.audit.bean.dto.UserCorrelation(p,i) " +
            "from Project1 p,AuditInstitution i " +
            "where p.institutions = i.id and p.id= ?1 ")
    UserCorrelation findItemById1(Long id);

    /**
     * 关联查询人员
     */
    @Query("select new cn.audit.bean.dto.UserCorrelation(u,r) " +
            "from User u,RoleManager r " +
            "where u.userRole = r.id ")
    List<UserCorrelation> findUserAll();

    /**
     * 角色分配
     */
    @Modifying(clearAutomatically = true)
    @Query("update Project1 p " +
            "set p.lord = ?2 ,p.adjudication = ?3 " +
            "where p.id = ?1 ")
    void updateProject(Long id, Long lord, Long adjudication);

    /**
     * 任务分配
     */
    @Modifying(clearAutomatically = true)
    @Query("update Project1 p " +
            "set p.taskoId = ?2 " +
            "where p.id = ?1 ")
    void updateItem(Long id, String ids);


    /**
     * 计划项目进度
     *
     * @return
     */
    /**
     * SELECT p.*,u.name,r.rolename FROM project p,t_user u,t_role_manager r
     * WHERE p.lord=u.id AND u.user_role=r.id
     *
     * @return
     */
    @Query(value = "select new cn.audit.bean.dto.UserCorrelation(u,r,p) " +
            "from User u,RoleManager r,Project1 p " +
            "where p.lord=u.id AND u.userRole=r.id")
    List<UserCorrelation> findProjectProgress();


    @Query(value = "select new cn.audit.bean.dto.UserCorrelation(u,u2,p) " +
            "from User u,User u2,Project1 p " +
            "where  p.lord=u.id AND p.adjudication=u2.id ")
    Page<UserCorrelation> findProject2(Pageable page);

    /**
     * 领导工作台 待办事项
     * 只查询审核中的数据
     *
     * @return
     */
    @Query(value = "select new cn.audit.bean.dto.UserCorrelation(u,p) " +
            "from User u,Project1 p " +
            "where p.projectgen=u.id " +
            "and p.status=2")
    List<UserCorrelation> findBacklog();


    /**
     * 根据id查找
     *
     * @param id
     * @return
     */
    Project1 findProject1ById(Long id);

    @Modifying(clearAutomatically = true)
    @Query(value = "update Project1 p set p.status = ?1 where p.id = ?2")
    void updateProject1Status(Long status, Long id);

}
